import os
import re
import PyPDF2
import xlwt
import pdfplumber
import traceback

# 获取当前文件夹中所有的PDF文件
HANDLE_PATH = '.'
pdf_files = [f for f in os.listdir(HANDLE_PATH) if os.path.isfile(f) and f.endswith('.pdf')]
path = ''
paths = "./转换之后/"
# 检查路径是否存在，如果不存在则创建
if not os.path.exists(paths):
    os.makedirs(paths)


def getTableData(table):
    data = []
    delivery_notice, material_number_idx, quality_confirmation_idx = -1, -1, -1
    for idx, row in enumerate(table):
        try:
            rowTxt = [cell if cell is not None else '' for cell in row]
            if '送货通知单' in ''.join(rowTxt):
                delivery_notice = row[-2]
            if '零件号' in row[0]:
                material_number_idx = idx
            if '质量确认' in row[0]:
                quality_confirmation_idx = idx
                break
        except:
            continue
    for row in table[material_number_idx + 1:quality_confirmation_idx - 1]:
        row[-1] = delivery_notice
        data.append(row)
    return data


for pdf_file in pdf_files:
    # 检查是否已经存在转换过的Excel文件，如果存在则跳过
    excel_file = pdf_file.replace('.pdf', '.xls')
    if os.path.exists(excel_file):
        continue

    with pdfplumber.open(pdf_file) as pdf:
        pdfTableData = []
        for page in pdf.pages:
            tables = page.extract_tables()
            for table in tables:
                # 找到零件号 - 质量确认之间的数据
                tableData = getTableData(table)
                if tableData:
                    pdfTableData.extend(tableData)
        print(pdfTableData)
        workbook = xlwt.Workbook()
        worksheet = workbook.add_sheet('供应商供货清单')
        info = [
            ('发运编号：', '', '', '', '', '', '客户：', '长春富维-江森自控汽车饰件系统有限公司济南分公司', '', '', ''),
            ('供应商', '', '', '', '', '', '采购订单编号：', '', '', '', ''),
            ('供应商地址：', '', '', '', '', '', '收货地址：', '', '', '', ''),
            ('序号', '零件号', '零件名称', '到货数量', '单位', '使用车型', '到货包装数', '标准包装数', '批次', '送货通知单号', '备注')
        ]
        font_style = xlwt.easyxf('font: height 280, bold on;')  # 设置字体大小为14，加粗
        borders = xlwt.Borders()  # 创建边框对象
        borders.left = xlwt.Borders.THIN
        borders.right = xlwt.Borders.THIN
        borders.top = xlwt.Borders.THIN
        borders.bottom = xlwt.Borders.THIN
        font_style.borders = borders  # 将边框应用到字体样式

        for row, row_data in enumerate(info):
            for col, cell_data in enumerate(row_data):
                worksheet.write(row + 1, col, cell_data, font_style)

        for col in range(11):  # 设置所有列的宽度为15个字符宽度
            worksheet.col(col).width = 256 * 23

        title_style = xlwt.easyxf('align: horiz center, vert center; font: height 280, bold on;')
        title_style.borders = borders
        worksheet.write_merge(0, 0, 0, 10, '供应商供货清单（国内供应商）', title_style)

        # 设置数据行的字体样式和边框
        data_style = xlwt.easyxf('font: height 280;')
        data_style.borders = borders
        for index, (part_number, part_name, item_number, unit, arrival_quantity, usage, packaging, standard_packaging, batch, delivery_notice) in enumerate(pdfTableData):
            # 序号	零件号	零件名称	到货数量	单位	使用车型	到货包装数	标准包装数	批次
            worksheet.write(index + 5, 0, index + 1, data_style)
            worksheet.write(index + 5, 1, part_number, data_style)
            worksheet.write(index + 5, 2, part_name, data_style)
            worksheet.write(index + 5, 3, arrival_quantity, data_style)
            worksheet.write(index + 5, 4, unit, data_style)  # 单位
            worksheet.write(index + 5, 5, usage, data_style)  # 使用车型
            worksheet.write(index + 5, 6, packaging, data_style)  # 到货包装数
            worksheet.write(index + 5, 7, standard_packaging, data_style)  # 标准包装数
            worksheet.write(index + 5, 8, batch, data_style)  # 批次
            worksheet.write(index + 5, 9, delivery_notice, data_style)
            worksheet.write(index + 5, 10, '', data_style)  # 备注

        workbook.save(paths + excel_file)
